package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.dao.AdvImageDao;
import com.fenrirtec.aepp.common.model.AdvImage;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;

public class AdvImageDao_JDBCImpl implements AdvImageDao {
	
	private static final Logger logger = LoggerFactory.getLogger(AdvImageDao_JDBCImpl.class);

	@Override
	public List<AdvImage> find(Boolean useFlag) {
		if (logger.isInfoEnabled()) {
			logger.info("[AdvImageDao_JDBCImpl#find] start.");
		}

		PreparedStatement statement = null;
		
		try {
			
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  image_id, \n")
				.append("  display_order, \n")
				.append("  use_flag, \n")
				.append("  title, \n")
				.append("  description, \n")
				.append("  path, \n")
				.append("  width, \n")
				.append("  height, \n")
				.append("  size, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag \n")
				.append("from \n")
				.append("  t_adv_image \n")
				.append("where \n")
				.append("  delete_flag = false \n");
			
			if (useFlag != null) {
				sql.append("and \n").append("  use_flag = ? \n");
			}
			
			sql.append("order by \n").append("  display_order \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[AdvImageDao_JDBCImpl#find] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			
			if (useFlag != null) {
				statement.setBoolean(1, useFlag);
			}
			
			ResultSet resultSet = statement.executeQuery();
			ResultSetMapper<AdvImage> resultSetMapper = new ResultSetMapper<AdvImage>();
			List<AdvImage> advImageList = resultSetMapper.mapResultSetToList(resultSet, AdvImage.class);
			
			return advImageList;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[AdvImageDao_JDBCImpl#find] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[AdvImageDao_JDBCImpl#find] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

    @Override
    public Boolean insert(AdvImage advImage) {
        if (logger.isInfoEnabled()) {
            logger.info("[AdvImageDao_JDBCImpl#insert] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            
            StringBuilder sql = new StringBuilder()
                .append("insert into t_adv_image ( \n")
                .append("  display_order, \n")
                .append("  use_flag, \n")
                .append("  title, \n")
                .append("  description, \n")
                .append("  path, \n")
                .append("  width, \n")
                .append("  height, \n")
                .append("  size, \n")
                .append("  create_user) \n")
                .append("values ( \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?) \n");
            
            if (logger.isTraceEnabled()) {
                logger.trace("[AdvImageDao_JDBCImpl#insert] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, advImage.getDisplayOrder());
            statement.setBoolean(2, advImage.getUseFlag());
            statement.setString(3, advImage.getTitle());
            statement.setString(4, advImage.getDescription());
            statement.setString(5, advImage.getPath());
            statement.setInt(6, advImage.getWidth());
            statement.setInt(7, advImage.getHeight());
            statement.setLong(8, advImage.getSize());
            statement.setString(9, advImage.getCreateUser());
            
            int count = statement.executeUpdate();
            if (count == 1) {
                result = true;
            }
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[AdvImageDao_JDBCImpl#insert] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[AdvImageDao_JDBCImpl#insert] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
        
        return result;
    }

    @Override
    public Boolean update(Integer imageId, Boolean useFlag, String updateUser) {
        if (logger.isInfoEnabled()) {
            logger.info("[AdvImageDao_JDBCImpl#update] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            
            StringBuilder sql = new StringBuilder()
                .append("update t_adv_image \n")
                .append("set \n")
                .append("  use_flag = ?, \n")
                .append("  update_user = ?, \n")
                .append("  update_date = current_timestamp \n")
                .append("where \n")
                .append("  image_id = ? \n");
            
            if (logger.isTraceEnabled()) {
                logger.trace("[AdvImageDao_JDBCImpl#update] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setBoolean(1, useFlag);
            statement.setString(2, updateUser);
            statement.setInt(3, imageId);
            
            int count = statement.executeUpdate();
            if (count == 1) {
                result = true;
            }
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[AdvImageDao_JDBCImpl#update] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[AdvImageDao_JDBCImpl#update] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
        
        return result;
    }

    @Override
    public Boolean delete(Integer imageId) {
        if (logger.isInfoEnabled()) {
            logger.info("[AdvImageDao_JDBCImpl#delete] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            
            StringBuilder sql = new StringBuilder()
                .append("delete from t_adv_image \n")
                .append("where \n")
                .append("  image_id = ? \n");
            
            if (logger.isTraceEnabled()) {
                logger.trace("[AdvImageDao_JDBCImpl#delete] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, imageId);
            
            int count = statement.executeUpdate();
            if (count == 1) {
                result = true;
            }
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[AdvImageDao_JDBCImpl#delete] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[AdvImageDao_JDBCImpl#delete] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
        
        return result;
    }

}
